﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.IO;
using System.Text;

namespace SampleTree
{
    public class DataAccess
    {

        private string _conn = "";
        private string _errmsg = "";
        private string _errsrc = "";
        private string _connType = "SQL";
        private string _providername = "System.Data.SqlClient";

        public DataAccess()
        {
            //
            // TODO: Add constructor logic here
            //
            // get connection string from webconfig

            //_conn = ConfigurationManager.ConnectionStrings["Application"].ConnectionString;

            _providername = "System.Data.SqlClient";

        }

        public DataAccess(string connstring)
        {
            _conn = connstring;

            _providername = "System.Data.SqlClient";
        }


        public string ProvideName
        {
            set { _providername = value; }
        }

        public string ConnectionString
        {
            get
            {
                return _conn;
            }
            set
            {
                _conn = value;
            }

        }

        public string ConnectionType
        {
            get
            {
                return _connType;
            }
            set
            {
                _connType = value;
            }
        }



        public string ErrorMessage
        {
            get
            {
                return _errmsg;
            }
        }

        public string ErrorSource
        {
            get
            {
                return _errsrc;
            }
        }


        public bool TestConnection(string connstring, string provider)
        {
            _conn = connstring;
            _providername = provider;

            string sqlquery = "select top 1 [name] from sysobjects";
            try
            {
                GetDataSet(sqlquery);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public SqlDataReader sqlDataReader(SqlConnection conn, string strSQL)
        {
            //using (SqlConnection conn = new SqlConnection(_conn))
            //{
            //    SqlCommand cmd = conn.CreateCommand();
            //    cmd.CommandText = strSQL;

            //    conn.Open();

            //    return cmd.ExecuteReader();
            //    //using (SqlDataReader dr = cmd.ExecuteReader())
            //    //{
            //    //    while (dr.Read())
            //    //        Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
            //    //}
            //}
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = strSQL;
            conn.Open();
            return cmd.ExecuteReader();
        }



        public OleDbDataReader oleDataReader(string strSQL)
        {
            using (OleDbConnection conn = new OleDbConnection(_conn))
            {
                OleDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = strSQL;
                conn.Open();
                return cmd.ExecuteReader();
            }



        }



        public object ExecSQLScalarSP(string storedProc, params object[] args)
        {
            object result;
            SqlDatabase sqlDb;
            DbCommand dbCommand = null;

            sqlDb = new SqlDatabase(_conn);

            try
            {
                dbCommand = sqlDb.GetStoredProcCommand(storedProc, args);
                result = sqlDb.ExecuteScalar(dbCommand);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in  ExecSQLScalarSP :" + storedProc + "\nError Message:" + ex.Message);
            }
            finally
            {
                if (dbCommand != null)
                {
                    if (dbCommand.Connection.State == ConnectionState.Open)
                        dbCommand.Connection.Close();
                }
            }
            return result;
        }

        public object ExecSQLScalar(string CommandText)
        {

            object obj;
            _errmsg = "";
            _errsrc = "";
            try
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(_providername);

                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = _conn;
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = CommandText;
                        connection.Open();
                        obj = command.ExecuteScalar();
                    }
                }

            }
            catch
            //catch (Exception ex)
            {
                //throw new Exception("ExecSQLScalar error : " + ex.Message + "\nSQL:" + CommandText);
                return null;
            }
            return obj;
        }


        public void FillDataSet(DataSet ds, string sql, string srcTable)
        {
            _errmsg = "";
            _errsrc = "";
            try
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(_providername);
                using (DbConnection conn = factory.CreateConnection())
                {
                    conn.ConnectionString = _conn;
                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = conn.CreateCommand();
                        adapter.SelectCommand.CommandText = sql;
                        adapter.Fill(ds, srcTable);
                    }
                }


                //da.Fill(ds, srcTable);

                //DbDataAdapter da = new DbDataAdapter
                //using (DbConnection connection = factory.CreateConnection())
                //{
                //    connection.ConnectionString = _conn;
                //    using (DbCommand command = connection.CreateCommand())
                //    {
                //        command.CommandText = CommandText;
                //        connection.Open();
                //        obj = command.ExecuteScalar();
                //    }
                //}

                //                Try
                //  conn.Open()
                //  Using reader As IDataReader = _
                //     cmd.ExecuteReader(CommandBehavior.CloseConnection)
                //    ' iterate through table rows and display the results
                //    Dim disc As Double = _ 
                //       Convert.ToDouble(cmd.Parameters("@discount").Value)
                //    output.Text &= "Products on sale with discount " & _
                //       "greater than " & disc.ToString("P") & ":<br />"
                //    While reader.Read()
                //      output.Text &= " - " & reader("Description") & "<br />"
                //    End While
                //  End Using
                //Catch ex As Exception
                //  output.Text &= "<br />* ERROR: " & ex.Message
                //End Try

            }
            catch (Exception ex)
            {
                throw new Exception("FillDataSet error : " + ex.Message + "\nSQL:" + sql);
            }
        }

        public int ExecuteSQL(string CommandText)
        {
            int i;
            _errmsg = "";
            _errsrc = "";
            try
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(_providername);

                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = _conn;
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = CommandText;
                        connection.Open();
                        i = command.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception ex)
            {
                throw new Exception("ExecuteSQL error : " + ex.Message + "\nSQL:" + CommandText);
            }
            return i;

        }

        public string ExcelOut(DataSet ds)
        {
            if (ds != null)
            {
                StringBuilder sb = new StringBuilder();
                StringBuilder sbRow = new StringBuilder();
                int j = 0, i = 0;
                DataRow dr;
                for (i = 0; i < ds.Tables[0].Columns.Count; i++)
                    sb.Append((i != 0 ? "\t" : "") + ds.Tables[0].Columns[i].Caption.ToString());

                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    sbRow = new StringBuilder();
                    dr = ds.Tables[0].Rows[i];
                    for (j = 0; j < ds.Tables[0].Columns.Count; j++)
                        sbRow.Append((j != 0 ? "\t" : "") + dr[j]);
                    sb.AppendLine();
                    sb.Append(sbRow.ToString());
                }
                return sb.ToString();
            }
            return "";
        }

        public string ExcelOut(string strSQL)
        {
            return ExcelOut(GetDataSet(strSQL));
        }

        public DataRow GetDataRow(string strSQL)
        {
            DataRow dr = null;
            DataSet ds = GetDataSet(strSQL);
            if (ds != null)
                if (ds.Tables[0].Rows.Count > 0) dr = ds.Tables[0].Rows[0];
            return dr;
        }

        public DataTable GetDataTable(string sql)
        {
            try
            {
                return GetDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception("GetDataTable(sql) error : " + ex.Message + "\nSQL:" + sql);
            }
        }



        public DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            DbProviderFactory factory = DbProviderFactories.GetFactory(_providername);

            try
            {

                using (DbConnection conn = factory.CreateConnection())
                {
                    conn.ConnectionString = _conn;
                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = conn.CreateCommand();
                        adapter.SelectCommand.CommandText = sql;
                        adapter.Fill(ds);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("GetDataSet error : " + ex.Message + "\nSQL:" + sql);
            }

            return ds;


        }

        //public DataSet GetDataSet(string strSQL, string srcTable)
        //{
        //    DataSet ds = null;
        //    DbProviderFactory factory = DbProviderFactories.GetFactory(_providername);

        //    try
        //    {

        //        using (DbConnection conn = factory.CreateConnection())
        //        {
        //            conn.ConnectionString = _conn;
        //            using (DbDataAdapter adapter = factory.CreateDataAdapter())
        //            {
        //                adapter.SelectCommand = conn.CreateCommand();
        //                adapter.SelectCommand.CommandText = strSQL;
        //                // ReSharper disable AssignNullToNotNullAttribute
        //                adapter.Fill(ds, srcTable);
        //                // ReSharper restore AssignNullToNotNullAttribute
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("GetDataSet(string strSQL, string srcTable) error : " + ex.Message + "\nSQL:" + strSQL);
        //    }

        //    return ds;
        //}

        public DataSet GetDataSet(string storedProc, params object[] args)
        {
            DataSet result;
            SqlDatabase sqlDb;
            DbCommand dbCommand = null;

            sqlDb = new SqlDatabase(_conn);

            try
            {
                dbCommand = sqlDb.GetStoredProcCommand(storedProc, args);
                result = sqlDb.ExecuteDataSet(dbCommand);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in  GetDataSet(string storedProc, params object[] args) :" + ex.Message + "\nSP:" + storedProc);
            }
            finally
            {
                if (dbCommand != null)
                {
                    if (dbCommand.Connection.State == ConnectionState.Open)
                        dbCommand.Connection.Close();
                }
            }
            return result;
        }


        public DataTable GetDataTable(string storedProc, params object[] args)
        {
            return GetDataSet(storedProc, args).Tables[0];
        }

        public DataRow GetDataRow(string storedProc, params object[] args)
        {
            DataTable dt = GetDataSet(storedProc, args).Tables[0];
            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            return null;
        }

        public void RunSqlTransaction(SqlDataAdapter da, SqlConnection myConnection, DataSet ds)
        {
            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = myConnection;
            myConnection.Open();
            SqlTransaction myTrans = myConnection.BeginTransaction();

            myCommand.Transaction = myTrans;

            try
            {
                da.Update(ds);
                myCommand.Transaction.Commit();
                Console.WriteLine("Update successful.");
            }
            catch (Exception e)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException ex)
                {
                    if (myTrans.Connection != null)
                    {
                        Console.WriteLine("An exception of type " + ex.GetType() +
                                          " was encountered while attempting to roll back the transaction.");
                    }
                }

                Console.WriteLine(e.ToString());
                Console.WriteLine("Update failed.");
            }
            myConnection.Close();
        }

        public int ExecuteNonQuery(string storedProc, params object[] args)
        {
            int result;
            SqlDatabase sqlDb;
            DbCommand dbCommand = null;

            sqlDb = new SqlDatabase(_conn);

            try
            {
                dbCommand = sqlDb.GetStoredProcCommand(storedProc, args);
                result = sqlDb.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in  ExecuteNonQuery :" + ex.Message);
            }
            finally
            {
                if (dbCommand != null)
                {
                    if (dbCommand.Connection.State == ConnectionState.Open)
                        dbCommand.Connection.Close();
                }
            }
            return result;
        }

        public static List<T> GetListTotalRows<T>(string connectionString, string storedProc, out int totalRows, params object[] args) where T : IDBPersist, new()
        {
            totalRows = 0;
            List<T> items = new List<T>();
            SqlDatabase sqlDb = null;
            DbCommand dbCommand = null;
            sqlDb = new SqlDatabase(connectionString);

            try
            {
                object[] argsWithRows = new object[args.Length + 1];
                for (int i = 0; i < args.Length; i++)
                {
                    argsWithRows[i] = args[i];
                }
                argsWithRows[args.Length] = totalRows;

                dbCommand = sqlDb.GetStoredProcCommand(storedProc, argsWithRows);

                using (IDataReader rdr = sqlDb.ExecuteReader(dbCommand))
                {
                    while (rdr.Read())
                    {
                        T item = new T();
                        item.PersistFromDatabase(rdr);
                        items.Add(item);
                    }
                }
                totalRows = (int)sqlDb.GetParameterValue(dbCommand, "TotalRows");
            }
            finally
            {
                if (dbCommand != null)
                {
                    if (dbCommand.Connection.State == ConnectionState.Open)
                        dbCommand.Connection.Close();
                }
            }

            return items;
        }

        public static List<T> GetList<T>(string connString, string storedProc, params object[] args) where T : IDBPersist, new()
        {
            List<T> items = new List<T>();
            SqlDatabase sqlDb = null;
            DbCommand dbCommand = null;

            sqlDb = new SqlDatabase(connString);

            try
            {

                dbCommand = sqlDb.GetStoredProcCommand(storedProc, args);
                using (IDataReader rdr = sqlDb.ExecuteReader(dbCommand))
                {
                    while (rdr.Read())
                    {
                        T item = new T();
                        item.PersistFromDatabase(rdr);
                        items.Add(item);
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (dbCommand != null)
                {
                    if (dbCommand.Connection.State == ConnectionState.Open)
                        dbCommand.Connection.Close();
                }
            }

            return items;

        }

        public static T GetObject<T>(string connString, string storedProc, params object[] args) where T : IDBPersist, new()
        {
            T item = new T();
            SqlDatabase sqlDb = null;
            DbCommand dbCommand = null;

            sqlDb = new SqlDatabase(connString);

            try
            {
                dbCommand = sqlDb.GetStoredProcCommand(storedProc, args);
                using (IDataReader rdr = sqlDb.ExecuteReader(dbCommand))
                {
                    if (rdr.Read())
                        item.PersistFromDatabase(rdr);
                }
            }
            finally
            {
                if (dbCommand != null)
                {
                    if (dbCommand.Connection.State == ConnectionState.Open)
                        dbCommand.Connection.Close();
                }
            }

            return item;
        }

        public void CopyData(DataTable sourceTable, SqlConnection destConnection, string desttablename, string mappingFrom, string mappingTo)
        {
            // new method: SQLBulkCopy:
            using (SqlBulkCopy s = new SqlBulkCopy(destConnection))
            {
                s.DestinationTableName = desttablename;
                //s.NotifyAfter = 10000;
                //s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);

                string[] colFrom = mappingFrom.Split(',');
                string[] colTo = mappingTo.Split(',');

                for (int i = 0; i < colTo.Length; i++)
                {
                    s.ColumnMappings.Add(colFrom[i], colTo[i]);
                    //s.ColumnMappings.Add("CarrierTrackingNumber", "TrackingNumber");
                }

                s.WriteToServer(sourceTable);
                s.Close();
            }
        }
    }


    public interface IDBPersist
    {
        int PersistFromDatabase(IDataReader rdr);
    }
}
